﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace Db
{
    public class MovieDb : AbstractDb
    {
        private PersonDb dudes = new PersonDb();
        private CinemaDb cinemas = new CinemaDb();



        /**
         * #############################################
         * ############### Movie Metoder ###############
         * #############################################
         */

        public List<Movie> GetAllMovies()
        {
            List<Movie> movies = new List<Movie>();

            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Movie");

            if (q != null && q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("movie_ID"));
                    string title = q.GetString(q.GetOrdinal("movie_title"));
                    string instructor = q.GetString(q.GetOrdinal("movie_instructor"));
                    string actors = q.GetString(q.GetOrdinal("movie_actors"));
                    int duration = q.GetInt32(q.GetOrdinal("movie_duration"));
                    string descrip = q.GetString(q.GetOrdinal("movie_description"));
                    byte active = q.GetByte(q.GetOrdinal("movie_active"));

                    Movie newMovie = new Movie(uid, title, instructor, duration, descrip, active, actors);
                    newMovie.Genres = this.GetAllGenreByMovieID(uid);
                    movies.Add(newMovie);
                }
            }
            return movies;
        }

        public List<Movie> GetAllActiveMovies()
        {
            List<Movie> movies = new List<Movie>();

            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Movie WHERE movie_active = 1");
            if (q != null && q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("movie_ID"));
                    string title = q.GetString(q.GetOrdinal("movie_title"));
                    string instructor = q.GetString(q.GetOrdinal("movie_instructor"));
                    string actors = q.GetString(q.GetOrdinal("movie_actors"));
                    int duration = q.GetInt32(q.GetOrdinal("movie_duration"));
                    string descrip = q.GetString(q.GetOrdinal("movie_description"));
                    byte active = q.GetByte(q.GetOrdinal("movie_active"));

                    Movie newMovie = new Movie(uid, title, instructor, duration, descrip, active, actors);
                    newMovie.Genres = this.GetAllGenreByMovieID(uid);
                    movies.Add(newMovie);
                }
            }
            return movies;
        }

        public Movie GetMovieByID(int id)
        {
            Movie movie = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Movie WHERE movie_ID = " + id);
            if (q.HasRows && q.Read())
            {
                int uid = q.GetInt32(q.GetOrdinal("movie_ID"));
                string title = q.GetString(q.GetOrdinal("movie_title"));
                string instructor = q.GetString(q.GetOrdinal("movie_instructor"));
                string actors = q.GetString(q.GetOrdinal("movie_actors"));
                int duration = q.GetInt32(q.GetOrdinal("movie_duration"));
                string descrip = q.GetString(q.GetOrdinal("movie_description"));
                byte active = q.GetByte(q.GetOrdinal("movie_active"));

                movie = new Movie(uid, title, instructor, duration, descrip, active, actors);
                movie.Genres = this.GetAllGenreByMovieID(uid);
            }
            return movie;
        }

        public List<Movie> FindMoviesByTitle(string title)
        {
            List<Movie> movies = new List<Movie>();
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Movie WHERE movie_title LIKE '%" + title + "%'");
            if (q != null && q.HasRows)
            {
                while (q.Read())
                {
                    int uid = q.GetInt32(q.GetOrdinal("movie_ID"));
                    string utitle = q.GetString(q.GetOrdinal("movie_title"));
                    string instructor = q.GetString(q.GetOrdinal("movie_instructor"));
                    string actors = q.GetString(q.GetOrdinal("movie_actors"));
                    int duration = q.GetInt32(q.GetOrdinal("movie_duration"));
                    string descrip = q.GetString(q.GetOrdinal("movie_description"));
                    byte active = q.GetByte(q.GetOrdinal("movie_active"));

                    Movie movie = new Movie(uid, utitle, instructor, duration, descrip, active, actors);
                    movie.Genres = this.GetAllGenreByMovieID(uid);

                    movies.Add(movie);
                }

            }
            return movies;
        }

        public int CreateMovie(Movie movie)
        {
            Statement stmt = new Statement("" +
                "INSERT INTO Bif_Movie" +
                "(movie_title, movie_instructor, movie_duration, movie_description, movie_active, movie_actors)" +
                "VALUES(@title, @instructor, @duration, @description, @active, @actors)");

            stmt.AddParameter("@title", movie.Title, "string");
            stmt.AddParameter("@instructor", movie.Instructor, "string");
            stmt.AddParameter("@duration", movie.Duration, "int");
            stmt.AddParameter("@description", movie.Description, "string");
            stmt.AddParameter("@active", movie.Active, "tinyint");
            stmt.AddParameter("@actors", movie.Actors, "string");

            int result = this.db.QueryInsert(stmt);
            movie.ID = result;

            if (result > 0)
            {
                this.UpdateGenresToMovie(result, movie.Genres);
            }

            return result;
        }

        public int UpdateMovie(Movie movie)
        {
            Statement stmt = new Statement("" +
                "UPDATE Bif_Movie " +
                "SET movie_title = @title, movie_instructor = @instructor, movie_duration = @duration, " +
                "movie_description = @description, movie_active = @active, movie_actors = @actors " +
                "WHERE movie_ID = @movieId");

            stmt.AddParameter("@title", movie.Title, "string");
            stmt.AddParameter("@instructor", movie.Instructor, "string");
            stmt.AddParameter("@duration", movie.Duration, "int");
            stmt.AddParameter("@description", movie.Description, "string");
            stmt.AddParameter("@active", movie.Active, "tinyint");
            stmt.AddParameter("@actors", movie.Actors, "string");

            stmt.AddParameter("@movieId", movie.ID, "int");


            int result = this.db.QueryUpdate(stmt);

            if (result > 0)
            {
                this.UpdateGenresToMovie(movie.ID, movie.Genres);
            }

            return result;
        }

        public void DeleteMovie(int movID)
        {
            this.db.QueryDelete("DELETE FROM Bif_Movie WHERE movie_ID =" + movID);
        }

        /**
          * #############################################
          * ############### Event Metoder ###############
          * #############################################
          */

        // TestLength and return " AND " if lenght > 0 else return ""
        private string TL(string testLength)
        {
            string test = "";
            if (testLength.Length > 0)
            {
                test = " AND ";
            }
            return test;
        }


        public List<Event> FindEvent(string title, string instructor, string actor, string duration, string playtime, List<string> genres)
        {
            List<Event> events = new List<Event>(); ;
                if (title.Length > 0 || instructor.Length > 0 || actor.Length > 0 || duration.Length > 0 || playtime.Length > 0 || (genres != null && genres.Count > 0))
                {
                    string findMovie = "";

                    if (title.Length > 0)
                    {
                        findMovie += " movie_title LIKE '%" + title + "%' ";
                    }
                    if (instructor.Length > 0)
                    {
                        findMovie += TL(findMovie) + " movie_instructor LIKE '%" + instructor + "%' ";
                    }
                    if (actor.Length > 0)
                    {
                        findMovie += TL(findMovie) + " movie_actors LIKE '%" + actor + "%' ";
                    }
                    if (duration.Length > 0)
                    {
                        findMovie += TL(findMovie) + " movie_duration LIKE '%" + duration + "%' ";
                    }
                    if (playtime.Length > 0)
                    {
                        findMovie += TL(findMovie) + " event_time LIKE '%" + playtime + "%' ";
                    }
                    if (genres != null && genres.Count > 0)
                    {
                        foreach (string genre in genres)
                        {
                            findMovie += TL(findMovie) + " genre_type = genre ";
                        }
                    }

                    Console.WriteLine(findMovie);

                    try
                    {
                        SqlDataReader q = this.db.Query("SELECT event_ID, event_time, event_price, event_seatsBooked," +
                        " event_seatsSold, event_movieID, event_cinemaID, movie_ID, movie_title, movie_duration, movie_actors, movie_instructor, genre_type" +
                        " FROM Bif_Movie " +
                        " LEFT JOIN Bif_Event ON event_movieID = movie_ID " +
                        " LEFT JOIN Bif_GenreMovie ON genreMovie_movID = movie_ID " +
                        " WHERE " + findMovie + " GROUP BY event_ID, event_time, event_price, event_seatsBooked," +
                        " event_seatsSold, event_movieID, event_cinemaID, movie_ID, movie_title, movie_duration, movie_actors, movie_instructor, genre_type");

                        while (q.Read())
                        {
                            //int uid = q.GetInt32(q.GetOrdinal("movie_ID"));
                            //string utitle = q.GetString(q.GetOrdinal("movie_title"));

                            int eID = q.GetInt32(q.GetOrdinal("event_ID"));
                            DateTime eTime = q.GetDateTime(q.GetOrdinal("event_time"));
                            double ePrice = q.GetDouble(q.GetOrdinal("event_price"));
                            int eSeatsBooked = q.GetInt32(q.GetOrdinal("event_seatsBooked"));
                            int eSeatsSold = q.GetInt32(q.GetOrdinal("event_seatsSold"));

                            int movieID = q.GetInt32(q.GetOrdinal("event_movieID"));
                            int cinemaID = q.GetInt32(q.GetOrdinal("event_cinemaID"));

                            Movie eMovie = GetMovieByID(movieID);
                            Cinema eCinema = cinemas.GetCinemaByID(cinemaID);

                            Event eve = new Event(eID, eTime, eMovie, eCinema, ePrice, eSeatsBooked, eSeatsSold);
                            events.Add(eve);
                        }
                    }
                    catch (Exception)
                    {
                    }
                    
                }
            return events;
        }





        /**
         * ##############################################
         * ############### Review Metoder ###############
         * ##############################################
         */


        public int CreateReview(Review review)
        {
            Statement stmt = new Statement("" +
                "INSERT INTO Bif_Review " +
                "(review_ID, review_personID, review_movieID, review_score, review_timestamp, review_review) " +
                "VALUES(@ID, @personID, @movieID, @score, @timestamp, @review)");

            stmt.AddParameter("@ID", review.ID, "int");
            stmt.AddParameter("@personID", review.Person.Id, "int");
            stmt.AddParameter("@movieID", review.Movie.ID, "int");
            stmt.AddParameter("@score", review.Rank, "int");
            stmt.AddParameter("@timestamp", review.Timestamp, "datetime");
            stmt.AddParameter("@review", review.Text, "string");

            return this.db.QueryInsert(stmt);
        }

        public Review GetReviewByID(int rewId)
        {
            Review review = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Review WHERE person_ID = " + rewId);
            if (q != null && q.HasRows)
            {

                int id = q.GetInt32(q.GetOrdinal("review_ID"));
                string text = q.GetString(q.GetOrdinal("review_review"));
                int rank = q.GetInt32(q.GetOrdinal("review_score"));
                DateTime timestamp = q.GetDateTime(q.GetOrdinal("review_timestamp"));

                int personID = q.GetInt32(q.GetOrdinal("review_personID"));
                int movieID = q.GetInt32(q.GetOrdinal("review_movieID"));

                Movie movie = GetMovieByID(movieID);
                Person person = dudes.GetPersonNamesByID(personID);

                review = new Review(id, text, rank, person, movie, timestamp);
            }
            return review;
        }

        public void DeleteReview(int revID)
        {
            SqlDataReader q = this.db.Query("DELETE FROM Bif_Review WHERE review_ID = revID");
        }

        public List<Review> GetAllReviewByMovieID(int movieID)
        {
            List<Review> reviews = new List<Review>();
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Review WHERE review_movieID = " + movieID);
            if (q != null && q.HasRows)
            {
                while (q.Read())
                {
                    int id = q.GetInt32(q.GetOrdinal("review_ID"));
                    string text = q.GetString(q.GetOrdinal("review_review"));
                    int rank = q.GetInt32(q.GetOrdinal("review_score"));
                    DateTime timestamp = q.GetDateTime(q.GetOrdinal("review_timestamp"));

                    int personID = q.GetInt32(q.GetOrdinal("review_personID"));

                    Movie movie = GetMovieByID(movieID);
                    Person person = dudes.GetPersonNamesByID(personID);

                    Review review = new Review(id, text, rank, person, movie, timestamp);
                    reviews.Add(review);
                }
            }
            return reviews;
        }


        /**
         * ##############################################
         * ############### GENRE Metoder ###############
         * ##############################################
         */


        public List<Genre> GetAllGenreByMovieID(int movieID)
        {
            List<Genre> genres = new List<Genre>();

            SqlDataReader q = this.db.Query("SELECT genre_type, genre_ID " +
                "FROM Bif_Genre " +
                "JOIN Bif_GenreMovie ON genreMovie_genID = genre_ID " +
                "JOIN Bif_Movie ON genreMovie_movID = movie_ID " +
                "WHERE movie_ID = " + movieID);

            if (q.HasRows)
            {
                while (q.Read())
                {
                    int id = q.GetInt32(q.GetOrdinal("genre_ID"));
                    string type = q.GetString(q.GetOrdinal("genre_type"));
                    Genre genre = new Genre(id, type);
                    genres.Add(genre);
                }
            }
            return genres;
        }

        public bool UpdateGenresToMovie(int movieId, List<Genre> genres)
        {
            bool result = false;

            if (movieId > 0)
            {
                Transaction trans = new Transaction();

                Statement stmt_del = new Statement("DELETE FROM Bif_GenreMovie WHERE genreMovie_movID = " + movieId + ";");
                trans.Add(stmt_del);

                if (genres.Count > 0)
                {
                    string queryAdd = "";
                    foreach (Genre g in genres)
                    {
                        if (queryAdd.Length > 0)
                        {
                            queryAdd += ", ";
                        }

                        queryAdd += "(" + movieId + ", " + g.ID + ")";
                    }
                    Statement stemt_ins = new Statement("INSERT INTO Bif_GenreMovie (genreMovie_movID, genreMovie_genID) VALUES " + queryAdd + ";");
                    trans.Add(stemt_ins);
                }

                result = this.db.CommitTransaction(trans);
            }

            return result;
        }

        public List<Genre> GetAllGenres()
        {
            List<Genre> genres = new List<Genre>();

            SqlDataReader q = this.db.Query("SELECT genre_type, genre_ID FROM Bif_Genre");

            if (q != null && q.HasRows)
            {
                while (q.Read())
                {
                    int id = q.GetInt32(q.GetOrdinal("genre_ID"));
                    string type = q.GetString(q.GetOrdinal("genre_type"));

                    Genre genre = new Genre(id, type);
                    genres.Add(genre);
                }
            }

            return genres;
        }



    }
}
